package com.lzl.datas.dao;

import com.lzl.common.GateWayPo;
import com.lzl.common.http.SimpleRequest;
import com.lzl.common.http.SimpleResponse;
import com.lzl.common.utils.SnowFlake;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Component
@Slf4j
public class DbDao {

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.noPool}")
    private Boolean noPool;

    @Value("${spring.datasource.autoCommit}")
    private Boolean autoCommit;

    /**
     * 数据访问接口.
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public boolean add(GateWayPo po) {

        final String insertSql = "insert into exchange (id, appId, status, datetime, request, response) values (?, ?, ?, ?, ?, ?)";
        final String appId = "1";
        final Long datetime = System.currentTimeMillis();

        Connection con = null;
        PreparedStatement st = null;
        try {
            con = getConnection();
            st = con.prepareStatement(insertSql);
            //当执行超过设置秒数，就会强制杀掉连接
            st.setQueryTimeout(1);
            InputStream bin = new ByteArrayInputStream(serialize(po.getRequest()));
            st.setLong(1, po.getId());
            st.setString(2, appId);
            st.setString(3, po.getStatus());
            st.setLong(4, datetime);
            st.setBinaryStream(5, bin, bin.available());
            st.setBinaryStream(6, null, 0);
            int result = st.executeUpdate();
            System.out.println(result);
            con.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
           colse(con, st, null);
        }

        return false;
    }

    private void colse(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean updateResponse(final Long id, final SimpleResponse response) {

        final String updateSql = "update exchange set status = ?, response = ? where id = ?";
        final String status = "1";

        Connection con = null;
        PreparedStatement st = null;
        try {
            con = getConnection();
            st = con.prepareStatement(updateSql);
            //当执行超过设置秒数，就会强制杀掉连接
            st.setQueryTimeout(1);
            InputStream bin = new ByteArrayInputStream(serialize(response));
            st.setString(1, status);
            st.setBinaryStream(2, bin, bin.available());
            st.setLong(3, id);
            int result = st.executeUpdate();
            System.out.println(result);
            con.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            colse(con, st, null);
        }
        return false;
    }

    public List<GateWayPo> getRequestList(Long lastId) {
        List<GateWayPo> datas = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            if (lastId == 0L) {
                preparedStatement = connection.prepareStatement("select id, request from exchange where appId = ? and status = '0' and datetime >= ? order by id");
                preparedStatement.setString(1, "1");
                Long time = System.currentTimeMillis() - (5 * 1000);
                preparedStatement.setLong(2, time);
                log.info("select id, request from exchange where appId = '1' and status = '0' and datetime >= " + time + " order by id");
                resultSet = preparedStatement.executeQuery();
            } else {
                preparedStatement = connection.prepareStatement("select id, request from exchange where appId = ? and status = '0' and id > ? order by id");
                preparedStatement.setString(1, "1");
                preparedStatement.setLong(2, lastId);
                log.info("select id, request from exchange where appId = '1' and status = '0' and id > " + lastId + " order by id");
                resultSet = preparedStatement.executeQuery();
            }


            while (resultSet.next()) {
                GateWayPo po = new GateWayPo();
                po.setId(resultSet.getLong("id"));
                InputStream in = resultSet.getBinaryStream("request");
                byte[] bs = IOUtils.toByteArray(in);
                SimpleRequest request = (SimpleRequest) unserializeObj(bs);
                po.setRequest(request);
                datas.add(po);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            colse(connection, preparedStatement, resultSet);
        }
        return  datas;
    }

    /**
     * 反序列化
     *
     * @param bytes
     * @return
     * @see [类、类#方法、类#成员]
     */
    public static Object unserializeObj(byte[] bytes)
    {
        ByteArrayInputStream byteInputStream = null;

        ObjectInputStream inputStream = null;

        try
        {
            byteInputStream = new ByteArrayInputStream(bytes);

            inputStream = new ObjectInputStream(byteInputStream);

            return inputStream.readObject();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            if (null != byteInputStream)
            {
                try
                {
                    byteInputStream.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                    return null;
                }
            }

            if (null != inputStream)
            {
                try
                {
                    inputStream.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                    return null;
                }
            }
        }
    }

    public static byte[] serialize(Object object) {
        ObjectOutputStream oos = null;
        ByteArrayOutputStream baos = null;
        // 序列化
        baos = new ByteArrayOutputStream();
        try {
            oos = new ObjectOutputStream(baos);
            oos.writeObject(object);
            byte[] bytes = baos.toByteArray();
            return bytes;
        } catch (IOException e) {
            // TODO Auto-generated catch block
            System.out.println(e.getMessage());
        } finally{
            try {
                oos.close();
                baos.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                System.out.println(e.getMessage());
            }

        }
        return null;
    }

    private Connection getConnection() throws SQLException {

        for(int i = 0 ; i < 3 ; ++i) {
            try {
                if(!noPool) {
                    String usr = username;
                    String pass = password;
                    String dburl = url;
                    Connection conn = DriverManager.getConnection(dburl,usr,pass);
                    conn.setAutoCommit(autoCommit);
                    return conn;
                }
                //LOG.info("自行获取JDBC连接 ");
                Connection conn =  jdbcTemplate.getDataSource().getConnection();
                conn.setAutoCommit(autoCommit);
                return conn;
            } catch(Throwable e) {
                log.info("数据库连接异常，重试...:" + e.getMessage());
                try {
                    Thread.sleep(500);
                } catch (InterruptedException e1) {
                    // TODO Auto-generated catch block
                }
            }
        }
        throw new SQLException("数据库连接尝试耗尽.");
    }


    public List<GateWayPo> getResponseList(List<Long> gids) {

        List<GateWayPo> datas = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();

            String ids = StringUtils.join(gids, ",");
            String sql = "select id, response from exchange where status = '1' and id in (" + ids + ")";
            log.info(sql);
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();


            while (resultSet.next()) {
                GateWayPo po = new GateWayPo();
                po.setId(resultSet.getLong("id"));
                InputStream in = resultSet.getBinaryStream("response");
                byte[] bs = IOUtils.toByteArray(in);
                SimpleResponse response = (SimpleResponse) unserializeObj(bs);
                po.setResponse(response);
                datas.add(po);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            colse(connection, preparedStatement, resultSet);
        }
        return  datas;
    }
}
